import pymysql
import os
import pandas as pd

class MySqlUtil():
    def __init__(self, config, statement=True):
        try:
            # 连接database
            self.conn = pymysql.connect(host=config['host'],
                                        user=config['user'],
                                        password=config['password'],
                                        port=int(config['port']),
                                        db=config['db'],
                                        charset=config['charset'])

            if 'prefix' in config:
                self.prefix = config['prefix']
            else:
                self.prefix = ''
        except Exception as e:
            print(f'数据库配置有误：{e}')
            os._exit(0)
        if statement: self.statement()

    def createtable(self, sql):
        if sql:
            self.conn.ping(reconnect=True)
            cursor = self.conn.cursor(buffered=True)
            cursor.execute(sql)
            cursor.close()
            return 'success'
        else:
            return 'error'

    def insert(self, sql):
        if sql:
            self.conn.ping(reconnect=True)
            cursor = self.conn.cursor()
            cursor.execute(sql)
            self.conn.commit()
            cursor.close()
            return 'success'
        else:
            return 'error'

    def delete(self, id, table='', sql=''):
        if sql:
            sql = sql
        elif table:
            sql = "delete from %s where id=%d" % (self.prefix + table, id)
        else:
            return 'error'
        self.conn.ping(reconnect=True)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        cursor.close()
        return 'success'

    def select(self, table='', sql=''):
        if sql:
            sql = sql
        elif table:
            sql = "select * from %s" % self.prefix + table
        else:
            return 'error'
        self.conn.ping(reconnect=True)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        cursor.close()
        return result

    def dbselect(self, sql=''):
        try:
            self.conn.ping(reconnect=True)
            result = pd.read_sql(sql, self.conn)
            return result
        except:
            return 'error'

    def update(self, sql):
        if sql:
            self.conn.ping(reconnect=True)
            cursor = self.conn.cursor()
            cursor.execute(sql)
            self.conn.commit()
            cursor.close()
            return 'success'
        else:
            return 'error'

    def statement(self):
        statement = f'{self.__class__.__name__}\n' \
                    '作者: 梦辰雪（Cui Mengchao）\n' \
                    '版本号: v1.0.1\n' \
                    '更新时间: 2024-05-19\n' \
                    'gitee: https://gitee.com/tsuimengchao\n'  \
                    '更新日志：\nv1.0.1：取消了配置文件，采用传参方式传递配置。'
        print(f'\033[1;33m{statement}\033[0m\n')